<?php

class Application_Model_RelatorioFaturamento extends Zend_Db_Table_Abstract {

    protected $_name = 'relatorio';
    protected $_primary = 'codigo';
    private static $instance;

    public static function getInstance() {
        if (!isset(self::$instance)) {
            self::$instance = new Application_Model_RelatorioFaturamento();
            return self::$instance;
        }
        return self::$instance;
    }

    public function lista($data) {

        //print_r($data);die;
        $initialDate = new Zend_Date(''.$data["dt_inicioAno"].'\'-'.$data["dt_inicioMes"].'-01');
        $finalDate = new Zend_Date(''.$data["dt_fimAno"].'\'-'.$data["dt_fimMes"].'-01');
        $lastDayofMonthInitial = $initialDate->get(Zend_Date::MONTH_DAYS);
        $lastDayofMonthFinal = $finalDate->get(Zend_Date::MONTH_DAYS);
        
        $sql = 'select count(dt_atendimento) as atendimentos, sum(a.valor) as valor, p.nome as nome, par.valor as pavor
                from atendimento a
                join pessoa as p on p.codigo = a.paciente
                join parceria as par on par.codigo = p.parceria
                where a.dt_exclusao is null';

        $dataInicial = $data["dt_inicioAno"].'-'.$data["dt_inicioMes"].'-01';
        $dataFinal = $data["dt_fimAno"].'-'.$data["dt_fimMes"].'-'.$lastDayofMonthFinal;

        

        if(isset($data['dt_inicioAno']) && !empty($data['dt_inicioAno'])){
            $sql.=' and dt_atendimento between \''.Util_Function_Util::formataData($dataInicial, 'Y/M/d').'\'
            and \''.Util_Function_Util::formataData($dataFinal, 'Y/M/d').'\'';
        }

        /*if(isset($data['dt_vencimento']) && !empty($data['dt_vencimento'])){
            $sql.=' and d.dt_vencimento = \''. Util_Function_Util::formataData($data['dt_vencimento'], 'Y/M/d'). '\'';
        }

        if(isset($data['dt_pagamento']) && !empty($data['dt_pagamento'])){
            $sql.=' and d.dt_pagamento = \''. Util_Function_Util::formataData($data['dt_pagamento'], 'Y/M/d'). '\'';
        }

        if(isset($data['tipo_despesa']) && !empty($data['tipo_despesa'])){
            $sql.=' and tp.descricao = \''. $data['tipo_despesa'].'\'';
        }

        /*if (isset($orderName) && isset($order)) {
            $sql .= ' order by d.' . $orderName . ' ' . $order;
        }*/

        $rs = $this->getAdapter()->query($sql)->fetchAll();

        return $rs;
    }

    public function lista2($data) {

        //print_r($data);die;
        $initialDate = new Zend_Date(''.$data["dt_inicioAno"].'\'-'.$data["dt_inicioMes"].'-01');
        $finalDate = new Zend_Date(''.$data["dt_fimAno"].'\'-'.$data["dt_fimMes"].'-01');
        $lastDayofMonthInitial = $initialDate->get(Zend_Date::MONTH_DAYS);
        $lastDayofMonthFinal = $finalDate->get(Zend_Date::MONTH_DAYS);

        $sql = 'select count(codigo) as pagamentos, sum(valor) as valor from despesa where dt_exclusao is null';

        $dataInicial = $data["dt_inicioAno"].'-'.$data["dt_inicioMes"].'-01';
        $dataFinal = $data["dt_fimAno"].'-'.$data["dt_fimMes"].'-'.$lastDayofMonthFinal;

        if(isset($data['dt_inicioAno']) && !empty($data['dt_inicioAno'])){
            $sql.=' and dt_pagamento between \''.Util_Function_Util::formataData($dataInicial, 'Y/M/d').'\'
            and \''.Util_Function_Util::formataData($dataFinal, 'Y/M/d').'\'';
        }

        $rs = $this->getAdapter()->query($sql)->fetchAll();
        return $rs;
    }
    
    public function GetMonth(){

        $month = array();
            
        $month[] = array("value" => "01", "mes" => "Janeiro");
        $month[] = array("value" => "02", "mes" => "Fevereiro");
        $month[] = array("value" => "03", "mes" => "Março");
        $month[] = array("value" => "04", "mes" => "Abril");
        $month[] = array("value" => "05", "mes" => "Maio");
        $month[] = array("value" => "06", "mes" => "Junho");
        $month[] = array("value" => "07", "mes" => "Julho");
        $month[] = array("value" => "08", "mes" => "Agosto");
        $month[] = array("value" => "09", "mes" => "Setembro");
        $month[] = array("value" => "10", "mes" => "Outubro");
        $month[] = array("value" => "11", "mes" => "Novembro");
        $month[] = array("value" => "12", "mes" => "Dezembro");

        return $month;

    }

    public function getYear(){

        $year = array();
        $today = date("Y");
        $currentYear = $today;

        for($i = 0; $i <= 20; $i++){
            $year[] = $currentYear;
            $currentYear -= 1;
        }
        //print_r($year);die;
        return $year;
    }

    
    
}